package cn.com.bo.util.mysql;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

/**
 * jdbc基础类
 * @author Zhang Shaobo
 *
 */
public class DBConn {
	
	private static Logger logger = Logger.getLogger(DBConn.class);
	
	public static final char UNDERLINE = '_'; 
    
    //三大核心接口
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    
    //四个方法
    //method1: 创建数据库的连接
    public Connection getConntion(){        
//        try {
            //1: 加载连接驱动，Java反射原理
//            Class.forName(Config.CLASS_NAME);
//            //2:创建Connection接口对象，用于获取MySQL数据库的连接对象。三个参数：url连接字符串    账号  密码
//            String url = Config.DATABASE_URL;
//            conn = DriverManager.getConnection(url,Config.USERNAME,Config.PASSWORD);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }    
        return conn;
    }
    
    
    //method2：关闭数据库的方法
    public void closeConn(){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    
    //method3: 专门用于发送增删改语句的方法
    public int execOther(PreparedStatement pstmt){
        try {
            //1、使用Statement对象发送SQL语句
            int affectedRows = pstmt.executeUpdate();
            //2、返回结果
            return affectedRows;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }


    //method4: 专门用于发送查询语句
    public ResultSet execQuery(PreparedStatement pstmt){
        try {
            //1、使用Statement对象发送SQL语句
            rs = pstmt.executeQuery();
            //2、返回结果
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    /** 
     * 按条件查找 
     * @since  2016年10月20日  
     * @param clazz 
     * @param sql 
     * @return 
     * @throws Exception 
     */  
    public <T> List<T> find(Class<T> clazz, String sql) {  
        if (sql == null || sql.length() == 0) {  
            logger.warn("查询sql语句不能为空");  
            return new ArrayList<T>();  
        }  
        logger.info("sql = {"+sql+"}");
        try {  
        	conn = getConntion();  
        	pstmt = conn.prepareStatement(sql);    
            rs = pstmt.executeQuery(sql);  
            return (List<T>) handler(clazz, rs);  
        } catch (Exception e) {  
            logger.error("sql = {"+sql+"}执行出错,Exception = {"+e.getLocalizedMessage()+"}");  
        } finally {  
        	closeConn();  
        }  
        return null;
    }  
    
    /** 
     * 组装list对象 
     * @author linbingwen 
     * @since  2016年10月20日  
     * @param clazz 
     * @param rs 
     * @return 
     * @throws Exception 
     */  
    private Object handler(Class clazz, ResultSet rs) throws Exception {  
        List list = new ArrayList();  
        try {  
            while (rs.next()) {  
                Object bean = clazz.newInstance();  
                
                ResultSetMetaData meta = rs.getMetaData();  
                int count = meta.getColumnCount();  
                for (int i = 0; i < count; i++) {  
                    String columnName = meta.getColumnName(i + 1);  
                    String name = columnName;  
                    if (columnName.contains(".")) {  
                        String[] split = columnName.split("\\.");  
                        if (split.length != 2) {  
                            throw  new Exception("输入的表名不正确！");  
                        }  
                        name =split[1];  
                    }  
                      
                    Object value = rs.getObject(columnName);  
                    name = underlineToCamel(name.toLowerCase());  
                    try {  
                        Field f = bean.getClass().getDeclaredField(name);  
                        if (f != null) {  
                            f.setAccessible(true);  
                            f.set(bean, value);  
                        }  
                    } catch (NoSuchFieldException e) {  
                        logger.error("表中字段：{"+name+"}在类：{"+clazz+"}没有对应的属性");  
                    } catch (IllegalArgumentException e) {  
                    	e.printStackTrace();
                        logger.error("表中字段：{"+name+"}在类：{"+clazz+"}对应属性类型不一致");  
                    }  
                }  
                list.add(bean);  
            }  
        } catch (Exception e) {  
        	e.printStackTrace();
            throw e;  
        }  
          
        logger.info("hiveHandler successed the total size is:{"+list.size()+"}");  
          
        return list;  
    }  
    
    /** 
     * 下划线字段转成陀峰字段 
     * @author linbingwen 
     * @since  2016年10月20日  
     * @param param 
     * @return 
     */  
    private String underlineToCamel(String param) {  
        if (param == null || param.isEmpty()) {  
            return null;  
        }  
        int len = param.length();  
        StringBuilder sb = new StringBuilder(len);  
        for (int i = 0; i < len; i++) {  
            char c = param.charAt(i);  
            if (c == UNDERLINE) {  
                if (++i < len) {  
                    sb.append(Character.toUpperCase(param.charAt(i)));  
                }  
            } else {  
                sb.append(c);  
            }  
        }  
        return sb.toString();  
    }  
    

}